package com.example.bootdemo.utils;

import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;

import java.util.List;
import java.util.Map;

public class ExcelUtil {

    /**
     * 创建excel文档，
     *
     * @param
     */
    public static HSSFWorkbook createWorkBook(List<List<String>> data) {
        // 生成一个样式,用于设置内容样式
        HSSFWorkbook workbook = new HSSFWorkbook();
        //设置Excel样式
        HSSFCellStyle bodyStyle = workbook.createCellStyle();
        bodyStyle.setWrapText(true);
        HSSFSheet sheet = workbook.createSheet("学生订单");
        for (int x = 0; x < data.size(); x++) {
            HSSFRow row = sheet.createRow(x);
            List<String> li = data.get(x);
            for (int y = 0; y < li.size(); y++) {
                String str = li.get(y);
                HSSFCell cellNum = row.createCell(y);
                cellNum.setCellStyle(bodyStyle);
                cellNum.setCellValue(new HSSFRichTextString(str));
            }
            row.setHeightInPoints(40);
        }
        CellRangeAddress region0 = new CellRangeAddress(0, 0, 0, 5);
        sheet.addMergedRegion(region0);
        CellRangeAddress region = new CellRangeAddress(1, 2, 0, 0);
        sheet.addMergedRegion(region);
        return workbook;
    }

    public static Workbook createWorkBook1(List<Map<String, Object>> data, String[] keys, String[] header) {
        String[] fieldNames = keys;
        Workbook wb = new HSSFWorkbook();
        int rowSize = 0;
        Sheet sheet = wb.createSheet();
        Row row = sheet.createRow(rowSize);
        // 生成一个样式,用于设置内容样式
        HSSFWorkbook workbook = new HSSFWorkbook();

        HSSFCellStyle cellStyle = workbook.createCellStyle();
        cellStyle.setWrapText(true);


        StringBuilder buffer = new StringBuilder();
        for (int i = 0; i < 5; i++) {
            buffer.append(" 2344455 (");
            buffer.append("yyyy-MM-dd HH:mm:ss" + ")");
            buffer.append("\r\n");
            buffer.append(" 1323445;");
            if (i != (5 - 1)) {
                buffer.append("\r\n");
            }
        }
        Cell cell = row.createCell(0);
        cell.setCellValue(buffer.toString());


        // cell.setCellValue(new HSSFRichTextString(buffer.toString()));

        //设置Excel样式
        HSSFCellStyle bodyStyle = workbook.createCellStyle();
        bodyStyle.setWrapText(true);

        // 设置文本值需要换行的位置添加:\r\n
        String strText = "测试写入Excel文本换行\r\n换行后的值";

        //使用HSSFRichTextString()处理文本值
        new HSSFRichTextString(strText);

        // 设置列的值和样式
        HSSFSheet sheet1 = workbook.createSheet("测试文本值换行");
        HSSFRow row2 = sheet1.createRow(0);
        HSSFCell cellNum = row2.createCell(2);
        cellNum.setCellStyle(bodyStyle);
        cellNum.setCellValue(new HSSFRichTextString(strText));


        try {
            for (int x = 0; x < data.size(); x++) {
                rowSize = 1;
                Row rowNew = sheet.createRow(rowSize + x);
                int i = 0;
                Map<String, Object> user = data.get(x);
                for (String key : user.keySet()) {
                    System.out.println("key= " + key + " and value= " + user.get(key));

                    rowNew.createCell(i).setCellValue(user.get(key).toString());
                    i++;
                }


            }
            //row.createCell(1).setCellStyle(cellStyle);
            CellRangeAddress region0 = new CellRangeAddress(0, 0, 0, 5);
            // sheet.addMergedRegion(region0);
            CellRangeAddress region = new CellRangeAddress(1, 2, 0, 0);
            // sheet.addMergedRegion(region);
            /**
             * 设置行的高度
             */
            row = sheet.getRow(0);
            row.setHeightInPoints(40);
            // 标题样式（加粗，垂直居中）

            //  row.createCell(4);
            //  Cell cell= row.getCell(4);;
       /* cell.setCellStyle(cellStyle);
        HSSFCell cell = row.createCell((short)0);*/
            // cell.setCellStyle(cellStyle);
            //  cell.setCellValue(new HSSFRichTextString("hello/r/n world!"));


        } catch (Exception e) {
            e.printStackTrace();
            e.getMessage();
        }


        return wb;

       /* // 创建excel工作簿
        SXSSFWorkbook wb = new SXSSFWorkbook(100);
        //在内存中只保留100行记录,超过100就将之前的存储到磁盘里
        // 创建第一个sheet（页），并命名
        Sheet sheet = wb.createSheet(list.get(0).get("sheetName").toString());
        // 手动设置列宽。第一个参数表示要为第几列设；，第二个参数表示列的宽度，n为列高的像素数。
        for(int i=0;i<keys.length;i++){
            sheet.setColumnWidth(i, (int) (35.7 * 150));
        }

        // 创建第一行
        Row row = sheet.createRow(0);

        // 创建两种单元格格式
        CellStyle cs = wb.createCellStyle();
        CellStyle cs2 = wb.createCellStyle();

        // 创建两种字体
        Font f = wb.createFont();
        Font f2 = wb.createFont();

        // 创建第一种字体样式（用于列名）
        f.setFontHeightInPoints((short)10);
        f.setColor(IndexedColors.BLACK.getIndex());

        // 创建第二种字体样式（用于值）
        f2.setFontHeightInPoints((short)10);
        f2.setColor(IndexedColors.BLACK.getIndex());


        //设置列名
        for(int i=0;i<columnNames.length;i++){
            Cell cell = row.createCell(i);
            cell.setCellValue(columnNames[i]);
            cell.setCellStyle(cs);
        }
        //设置每行每列的值
        for (int i = 1; i < list.size(); i++) {
            // Row 行,Cell 方格 , Row 和 Cell 都是从0开始计数的
            // 创建一行，在页sheet上
            Row row1 = sheet.createRow(i);
            // 在row行上创建一个方格
            for(int j=0;j<keys.length;j++){
                Cell cell = row1.createCell(j);
                cell.setCellValue(list.get(i).get(keys[j]) == null?" ": list.get(i).get(keys[j]).toString());
                cell.setCellStyle(cs2);
            }
        }
        return wb;*/
    }
}
